package util;

import constant.ChildWindowConstant;
import constant.CodeConstant;
import entity.Parameters;
import entity.TablesQueryModel;
import entity.cmnSys.CmSysButton;
import entity.cmnSys.CmSysMenu;
import org.postgresql.util.MD5Digest;

import javax.swing.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class DataUtils {

	/**
	 * @param columnsEng
	 */
	public static String getSqlParam(String columnsEng) {

		String sqlParamColumEng = "";

		if (columnsEng.contains(".")) {
			columnsEng = columnsEng.toLowerCase();
			String[] nameArr = columnsEng.split("\\.");
			sqlParamColumEng = makeSqlParam(nameArr);
		} else if (columnsEng.contains("_")) {
			columnsEng = columnsEng.toLowerCase();
			String[] nameArr = columnsEng.split("_");
			sqlParamColumEng = makeSqlParam(nameArr);
		} else if (columnsEng.contains("-")) {
			columnsEng = columnsEng.toLowerCase();
			String[] nameArr = columnsEng.split("-");
			sqlParamColumEng = makeSqlParam(nameArr);
		} else {
			if (columnsEng.matches("^[A-Z|0-9A-Z]+$")) {
				sqlParamColumEng = columnsEng.toLowerCase();
			} else {
				if (columnsEng.length() > 2) {
					sqlParamColumEng = columnsEng.substring(0, 2).toLowerCase() + columnsEng.substring(2);
				} else {
					sqlParamColumEng = columnsEng.toLowerCase();
				}

			}
		}

		return sqlParamColumEng;
	}

	private static String makeSqlParam(String[] nameArr) {

		String sqlParamColumEng = "";
		for (int j = 0; j < nameArr.length; j++) {
			if (j == 0) {
				sqlParamColumEng += nameArr[j];
			} else if (nameArr[j].length() > 1) {
				sqlParamColumEng += nameArr[j].substring(0, 1).toUpperCase() + nameArr[j].substring(1);
			} else {
				sqlParamColumEng += nameArr[j].toUpperCase();
			}
		}
		if (sqlParamColumEng.length() > 2) {
			sqlParamColumEng = sqlParamColumEng.substring(0, 2).toLowerCase() + sqlParamColumEng.substring(2);
		} else {
			sqlParamColumEng = sqlParamColumEng.toLowerCase();
		}
		return sqlParamColumEng;
	}

	/**
	 * 生成权限管理需要的表并初始化数据
	 *
	 * @param parameters 参数
	 */
	public static void makeAuth(Parameters parameters) {
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;
		try {
			connection = DBUtils.getConnection(parameters);
			assert connection != null;
			statement = connection.createStatement();
			if (CodeConstant.MYSQL.equals(parameters.getDataBaseTypeVal())) {
				//cm_sys_user
				createTable(statement,
						"select count(*)  from information_schema.tables t where t.table_schema ='" + parameters.getDataBaseNameVal() + "' and t.table_name ='cm_sys_user'",
						"CREATE TABLE `cm_sys_user` (\n" +
								"  `user_id` bigint(20) NOT NULL,\n" +
								"  `role_id` bigint(20) DEFAULT NULL,\n" +
								"  `username` varchar(30) DEFAULT NULL,\n" +
								"  `password` varchar(100) DEFAULT NULL,\n" +
								"  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,\n" +
								"  `update_time` datetime DEFAULT NULL,\n" +
								"  `create_user_id` bigint(20) DEFAULT NULL,\n" +
								"  `update_user_id` bigint(20) DEFAULT NULL,\n" +
								"  PRIMARY KEY (`user_id`)\n" +
								") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
				//cm_sys_menu
				createTable(statement,
						"select count(*)  from information_schema.tables t where t.table_schema ='" + parameters.getDataBaseNameVal() + "' and t.table_name ='cm_sys_menu'",
						"CREATE TABLE `cm_sys_menu` (\n" +
								"  `menu_id` bigint(20) NOT NULL,\n" +
								"  `parent_id` bigint(20) DEFAULT NULL,\n" +
								"  `order_no` bigint(20) DEFAULT NULL,\n" +
								"  `menu_name` varchar(30) DEFAULT NULL,\n" +
								"  `menu_icon` varchar(30) DEFAULT NULL,\n" +
								"  `url_address` varchar(100) DEFAULT NULL,\n" +
								"  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,\n" +
								"  `update_time` datetime DEFAULT NULL,\n" +
								"  `create_user_id` bigint(20) DEFAULT NULL,\n" +
								"  `update_user_id` bigint(20) DEFAULT NULL,\n" +
								"  PRIMARY KEY (`menu_id`)\n" +
								") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
				//cm_sys_role
				createTable(statement,
						"select count(*)  from information_schema.tables t where t.table_schema ='" + parameters.getDataBaseNameVal() + "' and t.table_name ='cm_sys_role'",
						"CREATE TABLE `cm_sys_role` (\n" +
								"  `role_id` bigint(20) NOT NULL,\n" +
								"  `role_name` varchar(30) DEFAULT NULL,\n" +
								"  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,\n" +
								"  `update_time` datetime DEFAULT NULL,\n" +
								"  `create_user_id` bigint(20) DEFAULT NULL,\n" +
								"  `update_user_id` bigint(20) DEFAULT NULL,\n" +
								"  PRIMARY KEY (`role_id`) USING BTREE\n" +
								") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
				//cm_sys_button
				createTable(statement,
						"select count(*)  from information_schema.tables t where t.table_schema ='" + parameters.getDataBaseNameVal() + "' and t.table_name ='cm_sys_button'",
						"CREATE TABLE `cm_sys_button` (\n" +
								"  `button_id` bigint(20) NOT NULL,\n" +
								"  `menu_id` bigint(20) NOT NULL,\n" +
								"  `button_name` varchar(30) DEFAULT NULL,\n" +
								"  `module_tag_id` varchar(30) DEFAULT NULL,\n" +
								"  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,\n" +
								"  `update_time` datetime DEFAULT NULL,\n" +
								"  `create_user_id` bigint(20) DEFAULT NULL,\n" +
								"  `update_user_id` bigint(20) DEFAULT NULL,\n" +
								"  PRIMARY KEY (`button_id`) USING BTREE\n" +
								") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
				//cm_sys_role_menu
				createTable(statement,
						"select count(*)  from information_schema.tables t where t.table_schema ='" + parameters.getDataBaseNameVal() + "' and t.table_name ='cm_sys_role_menu'",
						"CREATE TABLE `cm_sys_role_menu` (\n" +
								"  `role_menu_id` bigint(20) NOT NULL,\n" +
								"  `role_id` bigint(30) DEFAULT NULL,\n" +
								"  `menu_id` bigint(30) DEFAULT NULL,\n" +
								"  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,\n" +
								"  `update_time` datetime DEFAULT NULL,\n" +
								"  `create_user_id` bigint(20) DEFAULT NULL,\n" +
								"  `update_user_id` bigint(20) DEFAULT NULL,\n" +
								"  PRIMARY KEY (`role_menu_id`) USING BTREE\n" +
								") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
				//cm_sys_role_button
				createTable(statement,
						"select count(*)  from information_schema.tables t where t.table_schema ='" + parameters.getDataBaseNameVal() + "' and t.table_name ='cm_sys_role_button'",
						"CREATE TABLE `cm_sys_role_button` (\n" +
								"  `role_button_id` bigint(20) NOT NULL,\n" +
								"  `role_id` bigint(30) DEFAULT NULL,\n" +
								"  `button_id` bigint(30) DEFAULT NULL,\n" +
								"  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,\n" +
								"  `update_time` datetime DEFAULT NULL,\n" +
								"  `create_user_id` bigint(20) DEFAULT NULL,\n" +
								"  `update_user_id` bigint(20) DEFAULT NULL,\n" +
								"  PRIMARY KEY (`role_button_id`) USING BTREE\n" +
								") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
				//插入相关数据
				//TODO
			} else if (CodeConstant.ORACLE.equals(parameters.getDataBaseTypeVal())) {
				//cm_sys_user
				createTable(statement,
						"select count(*) from user_tables where table_name=upper('cm_sys_user')",
						"CREATE TABLE \"CM_SYS_USER\" \n" +
								"   (\t\"USER_ID\" NUMBER(20,0) NOT NULL ENABLE, \n" +
								"\t\"ROLE_ID\" NUMBER(20,0), \n" +
								"\t\"USERNAME\" NVARCHAR2(30), \n" +
								"\t\"PASSWORD\" NVARCHAR2(100), \n" +
								"\t\"CREATE_TIME\" DATE, \n" +
								"\t\"UPDATE_TIME\" DATE, \n" +
								"\t\"CREATE_USER_ID\" NUMBER(20,0), \n" +
								"\t\"UPDATE_USER_ID\" NUMBER(20,0), \n" +
								"\t PRIMARY KEY (\"USER_ID\"))");
				//cm_sys_menu
				createTable(statement,
						"select count(*) from user_tables where table_name=upper('cm_sys_menu')",
						"CREATE TABLE \"CM_SYS_MENU\" \n" +
								"   (\t\"MENU_ID\" NUMBER(20,0) NOT NULL ENABLE, \n" +
								"\t\"PARENT_ID\" NUMBER(20,0), \n" +
								"\t\"ORDER_NO\" NUMBER(20,0), \n" +
								"\t\"MENU_NAME\" NVARCHAR2(30), \n" +
								"\t\"MENU_ICON\" NVARCHAR2(30), \n" +
								"\t\"URL_ADDRESS\" NVARCHAR2(100), \n" +
								"\t\"CREATE_TIME\" DATE, \n" +
								"\t\"UPDATE_TIME\" DATE, \n" +
								"\t\"CREATE_USER_ID\" NUMBER(20,0), \n" +
								"\t\"UPDATE_USER_ID\" NUMBER(20,0), \n" +
								"\t PRIMARY KEY (\"MENU_ID\"))");
				//cm_sys_role
				createTable(statement,
						"select count(*) from user_tables where table_name=upper('cm_sys_role')",
						"CREATE TABLE \"CM_SYS_ROLE\" \n" +
								"   (\t\"ROLE_ID\" NUMBER(20,0) NOT NULL ENABLE, \n" +
								"\t\"ROLE_NAME\" NVARCHAR2(30), \n" +
								"\t\"CREATE_TIME\" DATE, \n" +
								"\t\"UPDATE_TIME\" DATE, \n" +
								"\t\"CREATE_USER_ID\" NUMBER(20,0), \n" +
								"\t\"UPDATE_USER_ID\" NUMBER(20,0), \n" +
								"\t PRIMARY KEY (\"ROLE_ID\"))");
				//cm_sys_button
				createTable(statement,
						"select count(*) from user_tables where table_name=upper('cm_sys_button')",
						"CREATE TABLE \"CM_SYS_BUTTON\" \n" +
								"   (\t\"BUTTON_ID\" NUMBER(20,0) NOT NULL ENABLE, \n" +
								"\t\"MENU_ID\" NUMBER(20,0) NOT NULL ENABLE, \n" +
								"\t\"BUTTON_NAME\" NVARCHAR2(30), \n" +
								"\t\"MODULE_TAG_ID\" NVARCHAR2(30), \n" +
								"\t\"CREATE_TIME\" DATE, \n" +
								"\t\"UPDATE_TIME\" DATE, \n" +
								"\t\"CREATE_USER_ID\" NUMBER(20,0), \n" +
								"\t\"UPDATE_USER_ID\" NUMBER(20,0), \n" +
								"\t PRIMARY KEY (\"BUTTON_ID\"))");
				//cm_sys_role_menu
				createTable(statement,
						"select count(*) from user_tables where table_name=upper('cm_sys_role_menu')",
						"CREATE TABLE \"CM_SYS_ROLE_MENU\" \n" +
								"   (\t\"ROLE_MENU_ID\" NUMBER(20,0) NOT NULL ENABLE, \n" +
								"\t\"ROLE_ID\" NUMBER(20,0), \n" +
								"\t\"MENU_ID\" NUMBER(20,0), \n" +
								"\t\"CREATE_TIME\" DATE, \n" +
								"\t\"UPDATE_TIME\" DATE, \n" +
								"\t\"CREATE_USER_ID\" NUMBER(20,0), \n" +
								"\t\"UPDATE_USER_ID\" NUMBER(20,0), \n" +
								"\t PRIMARY KEY (\"ROLE_MENU_ID\"))");
				//cm_sys_role_button
				createTable(statement,
						"select count(*) from user_tables where table_name=upper('cm_sys_role_button')",
						"CREATE TABLE \"CM_SYS_ROLE_BUTTON\" \n" +
								"   (\t\"ROLE_BUTTON_ID\" NUMBER(20,0) NOT NULL ENABLE, \n" +
								"\t\"ROLE_ID\" NUMBER(20,0), \n" +
								"\t\"BUTTON_ID\" NUMBER(20,0), \n" +
								"\t\"CREATE_TIME\" DATE, \n" +
								"\t\"UPDATE_TIME\" DATE, \n" +
								"\t\"CREATE_USER_ID\" NUMBER(20,0), \n" +
								"\t\"UPDATE_USER_ID\" NUMBER(20,0), \n" +
								"\t PRIMARY KEY (\"ROLE_BUTTON_ID\"))");
				//插入相关数据
				//TODO
			} else if (CodeConstant.POSTGRESQL.equals(parameters.getDataBaseTypeVal())) {
				rs = statement.executeQuery("select count(*) FROM information_schema.schemata WHERE schema_name = 'public';");
				rs.next();
				int count = rs.getInt(1);
				//如果没查到，证明没有这个表，创建public模式
				if (count == 0) {
					statement.executeUpdate("create schema public");
				}
				//cm_sys_user
				createTable(statement,
						"select count(*) from information_schema.tables where table_schema='public' AND table_name='cm_sys_user'",
						"CREATE TABLE \"public\".\"cm_sys_user\" (\n" +
								"  \"user_id\" int8 NOT NULL,\n" +
								"  \"role_id\" int8,\n" +
								"  \"username\" varchar(30) COLLATE \"pg_catalog\".\"default\",\n" +
								"  \"password\" varchar(100) COLLATE \"pg_catalog\".\"default\",\n" +
								"  \"create_time\" timestamp(6),\n" +
								"  \"update_time\" timestamp(6),\n" +
								"  \"create_user_id\" int8,\n" +
								"  \"update_user_id\" int8,\n" +
								"  CONSTRAINT \"cm_sys_user_pkey\" PRIMARY KEY (\"user_id\")\n" +
								")");
				//cm_sys_menu
				createTable(statement,
						"select count(*) from information_schema.tables where table_schema='public' AND table_name='cm_sys_menu'",
						"CREATE TABLE \"public\".\"cm_sys_menu\" (\n" +
								"  \"menu_id\" int8 NOT NULL,\n" +
								"  \"parent_id\" int8,\n" +
								"  \"order_no\" int8,\n" +
								"  \"menu_name\" varchar(30) COLLATE \"pg_catalog\".\"default\",\n" +
								"  \"menu_icon\" varchar(30) COLLATE \"pg_catalog\".\"default\",\n" +
								"  \"url_address\" varchar(100) COLLATE \"pg_catalog\".\"default\",\n" +
								"  \"create_time\" timestamp(6),\n" +
								"  \"update_time\" timestamp(6),\n" +
								"  \"create_user_id\" int8,\n" +
								"  \"update_user_id\" int8,\n" +
								"  CONSTRAINT \"cm_sys_menu_pkey\" PRIMARY KEY (\"menu_id\")\n" +
								")");
				//cm_sys_role
				createTable(statement,
						"select count(*) from information_schema.tables where table_schema='public' AND table_name='cm_sys_role'",
						"CREATE TABLE \"public\".\"cm_sys_role\" (\n" +
								"  \"role_id\" int8 NOT NULL,\n" +
								"  \"role_name\" varchar(30) COLLATE \"pg_catalog\".\"default\",\n" +
								"  \"create_time\" timestamp(6),\n" +
								"  \"update_time\" timestamp(6),\n" +
								"  \"create_user_id\" int8,\n" +
								"  \"update_user_id\" int8,\n" +
								"  CONSTRAINT \"cm_sys_role_pkey\" PRIMARY KEY (\"role_id\")\n" +
								")");
				//cm_sys_button
				createTable(statement,
						"select count(*) from information_schema.tables where table_schema='public' AND table_name='cm_sys_button'",
						"CREATE TABLE \"public\".\"cm_sys_button\" (\n" +
								"  \"button_id\" int8 NOT NULL,\n" +
								"  \"menu_id\" int8 NOT NULL,\n" +
								"  \"button_name\" varchar(30) COLLATE \"pg_catalog\".\"default\",\n" +
								"  \"module_tag_id\" varchar(30) COLLATE \"pg_catalog\".\"default\",\n" +
								"  \"create_time\" timestamp(6),\n" +
								"  \"update_time\" timestamp(6),\n" +
								"  \"create_user_id\" int8,\n" +
								"  \"update_user_id\" int8,\n" +
								"  CONSTRAINT \"cm_sys_button_pkey\" PRIMARY KEY (\"button_id\")\n" +
								")");
				//cm_sys_role_menu
				createTable(statement,
						"select count(*) from information_schema.tables where table_schema='public' AND table_name='cm_sys_role_menu'",
						"CREATE TABLE \"public\".\"cm_sys_role_menu\" (\n" +
								"  \"role_menu_id\" int8 NOT NULL,\n" +
								"  \"role_id\" int8,\n" +
								"  \"menu_id\" int8,\n" +
								"  \"create_time\" timestamp(6),\n" +
								"  \"update_time\" timestamp(6),\n" +
								"  \"create_user_id\" int8,\n" +
								"  \"update_user_id\" int8,\n" +
								"  CONSTRAINT \"cm_sys_role_menu_pkey\" PRIMARY KEY (\"role_menu_id\")\n" +
								")");
				//cm_sys_role_button
				createTable(statement,
						"select count(*) from information_schema.tables where table_schema='public' AND table_name='cm_sys_role_button'",
						"CREATE TABLE \"public\".\"cm_sys_role_button\" (\n" +
								"  \"role_button_id\" int8 NOT NULL,\n" +
								"  \"role_id\" int8,\n" +
								"  \"button_id\" int8,\n" +
								"  \"create_time\" timestamp(6),\n" +
								"  \"update_time\" timestamp(6),\n" +
								"  \"create_user_id\" int8,\n" +
								"  \"update_user_id\" int8,\n" +
								"  CONSTRAINT \"cm_sys_role_button_pkey\" PRIMARY KEY (\"role_button_id\")\n" +
								")");
				//插入相关数据
				authDataInit(connection);
			}
		} catch (Exception e) {
			LoggerUtil.error("权限管理建表时出错：" + e.getMessage());
			JOptionPane.showMessageDialog(null, "权限管理建表时出错：" + e.getMessage(), "错误",
					JOptionPane.ERROR_MESSAGE);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException ignored) {
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException ignored) {
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException ignored) {
				}
			}
		}
	}

	private static void createTable(Statement statement, String countSql, String createSql) {
		try {
			ResultSet rs = statement.executeQuery(countSql);
			rs.next();
			int count = rs.getInt(1);
			//如果没查到，证明没有这个表，创建
			if (count == 0) {
				statement.executeUpdate(createSql);
			}
		} catch (Exception e) {
			LoggerUtil.error("权限管理建表时出错：" + e.getMessage());
			JOptionPane.showMessageDialog(null, "权限管理建表时出错：" + e.getMessage(), "错误",
					JOptionPane.ERROR_MESSAGE);
		}
	}

	/**
	 * 初始化权限管理表的数据
	 *
	 * @param connection
	 */
	private static void authDataInit(Connection connection) {
		PreparedStatement pst = null;
		try {
			//初始化cm_sys_user表
			pst = connection.prepareStatement("INSERT INTO cm_sys_user(user_id,role_id,username,password) VALUES(?,?,?,?)");
			pst.setLong(1, 1);
			pst.setLong(2, 1);
			pst.setString(3, "admin");
			//前台需要传md5加密后的密码，后台再次加密后比对
			pst.setString(4, Md5Util.digest(Md5Util.digest("root")));
			pst.executeUpdate();
			//初始化cm_sys_role表
			pst = connection.prepareStatement("INSERT INTO cm_sys_role(role_id,role_name) VALUES(?,?)");
			pst.setLong(1, 1);
			pst.setString(2, "超级管理员");
			pst.executeUpdate();
			//初始化cm_sys_menu表
			List<CmSysMenu> cmSysMenus = new ArrayList<>(10);
			//查询所有单表 一个单表默认为一个一级菜单，同时包含一个二级子菜单
			for (Map.Entry<String, String> entry : ChildWindowConstant.currentTableCnNameMap.entrySet()) {
				//获取表英文名
				String tableEng = entry.getKey();
				//获取表中文名
				String tableCn = entry.getValue();
				//组装menu
				//一级菜单
				CmSysMenu menuParent = new CmSysMenu();
				menuParent.setMenuId(SnowflakeIdWorker.generateId());
				menuParent.setMenuName(tableCn);
				menuParent.setOrderNo(menuParent.getMenuId());
				menuParent.setMenuIcon("icon icon-chart");
				//二级地址菜单
				CmSysMenu menuChild = new CmSysMenu();
				menuChild.setMenuId(SnowflakeIdWorker.generateId());
				menuChild.setParentId(menuParent.getMenuId());
				menuChild.setOrderNo(menuChild.getMenuId());
				menuChild.setMenuName("信息管理");
				menuChild.setUrlAddress(tableEng + "/list");
				//添加到cmSysMenus中
				cmSysMenus.add(menuParent);
				cmSysMenus.add(menuChild);
			}
			//查询所有多表 模块名为一级菜单 里面的map 每个map里的方法为一个二级菜单
			for (Map.Entry<String, Map<String, TablesQueryModel>> entry : ChildWindowConstant.tablesQueryMap.entrySet()) {
				//获取模块英文名
				String modelNameEng = entry.getKey();
				//获取模块中文名
				String modelNameCn = ChildWindowConstant.tablesQueryEndAndCnMap.get(modelNameEng);
				//添加模块一级菜单
				CmSysMenu menuParent = new CmSysMenu();
				menuParent.setMenuId(SnowflakeIdWorker.generateId());
				menuParent.setMenuName(modelNameCn);
				menuParent.setOrderNo(menuParent.getMenuId());
				menuParent.setMenuIcon("icon icon-chart");
				//添加到cmSysMenus中
				cmSysMenus.add(menuParent);
				//获取模块下的所有方法，添加二级菜单
				Map<String, TablesQueryModel> methodMap = entry.getValue();
				for (Map.Entry<String, TablesQueryModel> methodEntry : methodMap.entrySet()) {
					//添加二级菜单
					CmSysMenu menuChild = new CmSysMenu();
					menuChild.setMenuId(SnowflakeIdWorker.generateId());
					menuChild.setParentId(menuParent.getMenuId());
					menuChild.setOrderNo(menuChild.getMenuId());
					menuChild.setMenuName(methodEntry.getValue().getMethodName_cn());
					menuChild.setUrlAddress(modelNameEng + "Muti/" + methodEntry.getKey() + "/list");
					//添加到cmSysMenus中
					cmSysMenus.add(menuChild);
				}
			}
			//按钮列表
			List<CmSysButton> cmSysButtons = new ArrayList<>(10);
			//插入cm_sys_menu表
			for (CmSysMenu cmSysMenu : cmSysMenus) {
				pst = connection.prepareStatement("INSERT INTO cm_sys_menu(menu_id,parent_id,order_no,menu_name,menu_icon,url_address) VALUES(?,?,?,?,?,?)");
				pst.setLong(1, cmSysMenu.getMenuId());
				pst.setLong(2, cmSysMenu.getParentId());
				pst.setLong(3, cmSysMenu.getOrderNo());
				pst.setString(4, cmSysMenu.getMenuName());
				pst.setString(5, cmSysMenu.getMenuIcon());
				pst.setString(6, cmSysMenu.getUrlAddress());
				pst.executeUpdate();
				//插入cm_sys_role_menu表
				pst = connection.prepareStatement("INSERT INTO cm_sys_role_menu(role_menu_id,role_id,menu_id) VALUES(?,?,?)");
				pst.setLong(1, SnowflakeIdWorker.generateId());
				pst.setLong(2, 1);
				pst.setLong(3, cmSysMenu.getMenuId());
				//插入cm_sys_button表（需要在模板标签上添加moduleTagId属性）
				//如果是二级菜单，则插入按钮
				if (cmSysMenu.getParentId() != null) {
					//查询按钮
					CmSysButton cmSysButtonQuery = new CmSysButton();
					cmSysButtonQuery.setButtonId(SnowflakeIdWorker.generateId());
					cmSysButtonQuery.setMenuId(cmSysMenu.getMenuId());
					cmSysButtonQuery.setButtonName("查询");
					cmSysButtonQuery.setModuleTagId("queryInfo");
					//添加到按钮列表
					cmSysButtons.add(cmSysButtonQuery);
					//如果是单表查询，插入增删改按钮
					if (!cmSysMenu.getUrlAddress().contains("Muti")) {
						//添加按钮
						CmSysButton cmSysButtonAdd = new CmSysButton();
						cmSysButtonAdd.setButtonId(SnowflakeIdWorker.generateId());
						cmSysButtonAdd.setMenuId(cmSysMenu.getMenuId());
						cmSysButtonAdd.setButtonName("添加数据");
						cmSysButtonAdd.setModuleTagId("addMsg");
						//添加按钮
						CmSysButton cmSysButtonUpdate = new CmSysButton();
						cmSysButtonUpdate.setButtonId(SnowflakeIdWorker.generateId());
						cmSysButtonUpdate.setMenuId(cmSysMenu.getMenuId());
						cmSysButtonUpdate.setButtonName("更新");
						cmSysButtonUpdate.setModuleTagId("upMsg");
						//删除按钮
						CmSysButton cmSysButtonDelete = new CmSysButton();
						cmSysButtonDelete.setButtonId(SnowflakeIdWorker.generateId());
						cmSysButtonDelete.setMenuId(cmSysMenu.getMenuId());
						cmSysButtonDelete.setButtonName("删除");
						cmSysButtonDelete.setModuleTagId("delMsg");
						//加入列表
						cmSysButtons.add(cmSysButtonAdd);
						cmSysButtons.add(cmSysButtonUpdate);
						cmSysButtons.add(cmSysButtonDelete);
					}
				}
				//插入cm_sys_button表
				for (CmSysButton cmSysButton : cmSysButtons) {
					pst = connection.prepareStatement("INSERT INTO cm_sys_button(button_id,menu_id,button_name,module_tag_Id) VALUES(?,?,?,?)");
					pst.setLong(1, cmSysButton.getButtonId());
					pst.setLong(2, cmSysButton.getMenuId());
					pst.setString(3, cmSysButton.getButtonName());
					pst.setString(4, cmSysButton.getModuleTagId());
					//插入cm_sys_role_button表
					pst = connection.prepareStatement("INSERT INTO cm_sys_role_button(role_button_id,button_id,role_id) VALUES(?,?,?)");
					pst.setLong(1, SnowflakeIdWorker.generateId());
					pst.setLong(2,cmSysButton.getButtonId());
					pst.setLong(3,1);
				}
			}
		} catch (Exception e) {
			LoggerUtil.error("权限管理初始化数据时出错：" + e.getMessage());
			JOptionPane.showMessageDialog(null, "权限管理初始化数据时出错：" + e.getMessage(), "错误",
					JOptionPane.ERROR_MESSAGE);
		} finally {
			if (pst != null) {
				try {
					pst.close();
				} catch (SQLException ignored) {
				}
			}
		}
	}
}
